package com.suwenhao.dao;


import com.suwenhao.entity.Dept;
import com.suwenhao.entity.Page;
import com.suwenhao.entity.User;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.BeanPropertyRowMapper;

import java.util.List;

/**
 * @Author:suwenhao
 * @Description: 测试
 * @Date: Created in 2020/9/22 17:35
 */

public class UserDao extends BaseDao {

    public User checkLogin(String username, String password) {
        String sql = "select *from user where username=? and password=?";
        try {
            return jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<>(User.class), username, password);
        } catch (Exception e) {
//            e.printStackTrace();
            return null;
        }
    }

    public List<User> userList(String username, Page page) {
        String str = "SELECT " +
                " u.id, " +
                " u.username, " +
                " u.email, " +
                " u.real_name, " +
                " u.age, " +
                " u.gender, " +
                " u.register_time, " +
                " d.NAME AS deptName, " +
                " CASE " +
                " gender " +
                " WHEN 0 THEN " +
                " '女' " +
                " WHEN 1 THEN " +
                " '男' ELSE '其他' " +
                " END sex " +
                " FROM " +
                " USER u " +
                " LEFT JOIN dept d ON u.dept_id = d.id  " +
                " where username like ? " +
                "limit ?,? ";


        try {
            return jdbcTemplate.query(str, new BeanPropertyRowMapper<>(User.class), "%" + username + "%",
                    (page.getPageCurrent()-1)*page.getSize(),page.getSize());
        } catch (DataAccessException e) {
            e.printStackTrace();
            return null;
        }
    }

    //计算页
    public Integer count(String name){
        String str="SELECT count(*) rowCount from  user where username like ? ";
            return jdbcTemplate.queryForObject(str,Integer.class,"%"+name+"%");
    }


    public void add(User user) {
        String sql = "INSERT INTO user (id, username,email,real_name) VALUES ( NULL,?,?,?)";
        jdbcTemplate.update(sql, user.getUsername(),user.getEmail(),user.getRealName());
    }



    //回写部门
    public List<Dept> listDept() {
        String str = "select *  from dept";
        return jdbcTemplate.query(str, new BeanPropertyRowMapper<>(Dept.class));
    }

    //删除
    public void delete(Integer id) {
        String sql = "delete from user where id=?";
        jdbcTemplate.update(sql, id);

    }

    //数据回显
    public User getUserById(Integer id) {
        String str = "select * from user where id=?";
        try {
            return jdbcTemplate.queryForObject(str, new BeanPropertyRowMapper<>(User.class),id);
        } catch (DataAccessException e) {
            return null;
        }
    }

    //修改数据回写数据
    public void update(User user){
        String str = "update user set username=? where id=?";
        jdbcTemplate.update(str,user.getUsername(),user.getId());
    }



    //修改数据库的pic
    public void updatePic(Integer id, String pic) {
        String str = "update user set pic=? where id=?";
        jdbcTemplate.update(str, pic, id);
    }

    //通过部门id查询该部门下的所有员工
    public List<User> getUserByDeptId(Integer id) {
        String str = "select * from user where dept_id=?";
        return jdbcTemplate.query(str, new BeanPropertyRowMapper<>(User.class), id);
    }

    public void updatePassword(User user){
        String str = "update user set password=? where username=?";
        jdbcTemplate.update(str,user.getPassword(),user.getUsername());
    }


    //微信
    public User weChatAccount(String wx_openid ) {
        String str = "select *  from user where wx_openid=?";
       return jdbcTemplate.queryForObject(str,new BeanPropertyRowMapper<>(User.class),wx_openid);
    }
}
